﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using ModelLayer;

namespace DBLayer
{
    public class RoomDBClient : IRoomDB
    {
        //CinemaDBClient cinemaDBClient = new CinemaDBClient();
        private static SqlCommand dbCmd;

        public Room GetRoom(int id)
        {
            //string sql = "SELECT id, name, seats, cinemaID FROM Rooms WHERE id = " + id + " ORDER BY id ASC;";
            string sql = "SELECT Room.id, Room.name, Room.seats, Room.cinemaID, Cinema.name AS cinemaName, Cinema.address AS cinemaAddress FROM Room JOIN Cinema ON Room.cinemaID = Cinema.id WHERE Room.id = " + id + ";";

            dbCmd = AccessDBSQLClient.GetDbCommand(sql);
            IDataReader dbReader = dbCmd.ExecuteReader();

            Room room;
            if (dbReader.Read())
            {
                //room = new Room(Convert.ToInt32(dbReader["id"].ToString()), dbReader["name"].ToString(), Convert.ToInt32(dbReader["seats"].ToString()), cinemaDBClient.GetCinema(Convert.ToInt32(dbReader["cinemaID"].ToString())));
                room = new Room(
                    Convert.ToInt32(dbReader["id"].ToString()),
                    dbReader["name"].ToString(),
                    Convert.ToInt32(dbReader["seats"].ToString()),
                    new Cinema(
                        Convert.ToInt32(dbReader["cinemaID"].ToString()),
                        dbReader["cinemaName"].ToString(),
                        dbReader["cinemaAddress"].ToString()
                    )
                );
            }
            else
            {
                room = null;
            }

            dbReader.Close();
            dbCmd.Connection.Close();

            return room;
        }

        public List<Room> GetRooms()
        {
            //string sql = "SELECT id, name, seats, cinemaID FROM Room ORDER BY id ASC;";
            string sql = "SELECT Room.id, Room.name, Room.seats, Room.cinemaID, Cinema.name AS cinemaName, Cinema.address AS cinemaAddress FROM Room JOIN Cinema ON Room.cinemaID = Cinema.id ORDER BY Room.id ASC;";

            dbCmd = AccessDBSQLClient.GetDbCommand(sql);
            IDataReader dbReader = dbCmd.ExecuteReader();

            List<Room> rooms = new List<Room>();
            while (dbReader.Read())
            {
                rooms.Add(
                    new Room(
                        Convert.ToInt32(dbReader["id"].ToString()),
                        dbReader["name"].ToString(),
                        Convert.ToInt32(dbReader["seats"].ToString()),
                        new Cinema(
                            Convert.ToInt32(dbReader["cinemaID"].ToString()),
                            dbReader["cinemaName"].ToString(),
                            dbReader["cinemaAddress"].ToString()
                        )
                        //cinemaDBClient.GetCinema(Convert.ToInt32(dbReader["cinemaID"].ToString()))
                    )
                );
            }

            dbReader.Close();
            dbCmd.Connection.Close();

            return rooms;
        }

        public List<Room> GetRooms(Cinema cinema)
        {
            string sql = "SELECT id, name, seats, cinemaID FROM Room WHERE cinemaID = " + cinema.Id + " ORDER BY id ASC;";
            //string sql = "SELECT Room.id, Room.name, Room.seats, Room.cinemaID, Cinema.name AS cinemaName, Cinema.address AS cinemaAddress FROM Room JOIN Cinema ON Room.cinemaID = Cinema.id WHERE Room.cinemaID = " + cinema.Id + " ORDER BY Room.id ASC;";

            dbCmd = AccessDBSQLClient.GetDbCommand(sql);
            IDataReader dbReader = dbCmd.ExecuteReader();

            List<Room> rooms = new List<Room>();
            while (dbReader.Read())
            {
                rooms.Add(
                    new Room(
                        Convert.ToInt32(dbReader["id"].ToString()), 
                        dbReader["name"].ToString(), 
                        Convert.ToInt32(dbReader["seats"].ToString()), 
                        cinema
                        //cinemaDBClient.GetCinema(Convert.ToInt32(dbReader["cinemaID"].ToString()))
                        //new Cinema(Convert.ToInt32(dbReader["cinemaID"].ToString()), dbReader["cinemaName"].ToString(), dbReader["cinemaAddress"].ToString())
                    )
                );
            }

            dbReader.Close();
            dbCmd.Connection.Close();

            return rooms;
        }

        public int AddRoom(Room room)
        {
            string sql = "INSERT INTO Room (name, seats, cinemaID) VALUES ('" + room.Name + "', " + room.Seats + ", " + room.Cinema.Id + ");";

            return AccessDBSQLClient.ExecuteQuery(sql);
        }

        public int EditRoom(Room room)
        {
            string sql = "UPDATE Room SET name = '" + room.Name + "', seats = " + room.Seats + ", cinemaID = " + room.Cinema.Id + " WHERE id = " + room.Id + ";";

            return AccessDBSQLClient.ExecuteQuery(sql);
        }

        public int RemoveRoom(int id)
        {
            string sql = "DELETE FROM Room WHERE id = " + id + ";";

            return AccessDBSQLClient.ExecuteQuery(sql);
        }
    }
}
